# db_handler.py
import MySQLdb
import MySQLdb.cursors
import configparser
import os

class MySQLHandler:
    def __init__(self, host, user, password, database, port=3306, charset='utf8'):
        self.host = host
        self.user = user
        self.password = password
        self.database = database
        self.port = port
        self.charset = charset
        self.connection = None

    def connect(self):
        if self.connection is None:
            self.connection = MySQLdb.connect(
                host=self.host,
                user=self.user,
                passwd=self.password,
                db=self.database,
                port=self.port,
                charset=self.charset,
                cursorclass=MySQLdb.cursors.DictCursor
            )

    def close(self):
        if self.connection:
            self.connection.close()
            self.connection = None

    def execute_query(self, query, params=None):
        self.connect()
        cursor = self.connection.cursor()
        try:
            cursor.execute(query, params)
            result = cursor.fetchall()
            return result
        except MySQLdb.Error as e:
            print(f"Error executing query: {e}")
            return None
        finally:
            cursor.close()
            self.close()

    def execute_update(self, query, params=None):
        self.connect()
        cursor = self.connection.cursor()
        try:
            cursor.execute(query, params)
            self.connection.commit()
            return cursor.rowcount
        except MySQLdb.Error as e:
            self.connection.rollback()
            print(f"Error executing update: {e}")
            return None
        finally:
            cursor.close()
            self.close()

    def last_insert_id(self):
        return self.execute_query("SELECT LAST_INSERT_ID()")[0]['LAST_INSERT_ID()']

    def insert(self, query, params=None):
        return self.execute_update(query, params)

    def update(self, query, params=None):
        return self.execute_update(query, params)

    def delete(self, query, params=None):
        return self.execute_update(query, params)

    def update_or_insert_server(self, ip, username, password, centos_type, jvmXms, jvmXmx, status, is_select,
                                lock_user):
        self.connect()
        cursor = self.connection.cursor()
        try:
            # 检查是否已经存在相同的 IP 地址
            cursor.execute("SELECT * FROM slaver WHERE ip = %s", (ip,))
            record = cursor.fetchone()
            if record:
                # 更新现有记录的数据
                cursor.execute("""
                    UPDATE slaver SET 
                    username=%s, password=%s, centos_type=%s, jvmXms=%s, jvmXmx=%s, status=%s, is_select=%s, lock_user=%s
                    WHERE ip=%s
                """, (username, password, centos_type, jvmXms, jvmXmx, status, is_select, lock_user, ip))
            else:
                # 插入新的记录
                cursor.execute("""
                    INSERT INTO slaver (ip, username, password, centos_type, jvmXms, jvmXmx, status, is_select, lock_user)
                    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
                """, (ip, username, password, centos_type, jvmXms, jvmXmx, status, is_select, lock_user))
            self.connection.commit()
        except MySQLdb.Error as e:
            print(f"Error: {e}")
            self.connection.rollback()
        finally:
            cursor.close()
            self.close()

# 读取配置文件
config = configparser.ConfigParser()
config_path = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'static', 'db.ini')
config.read(config_path)
#config.read('static\db.ini')

# 获取数据库配置
db_config = {
    'host': config.get('database', 'host'),
    'user': config.get('database', 'user'),
    'password': config.get('database', 'password'),
    'database': config.get('database', 'database'),
    'port': config.getint('database', 'port'),
    'charset': config.get('database', 'charset')
}

# 创建 MySQLHandler 实例
db_handler = MySQLHandler(**db_config)